﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Automobile.service.Common;
using Automobile.service.Model;
using System.Data;
using System.Data.SqlClient;

namespace Automobile.service.DAL
{
    public class StatisticsDAL
    {
        SQLHelper h = new SQLHelper();
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="Year">年</param>
        /// <param name="MonthStart">开始月份</param>
        /// <param name="MonthEnd">结束也分</param>
        /// <param name="PurChase_InOut">类型</param>
        /// <returns></returns>
        public List<Purchase> SearchPurchaseBydate(string DateStart,string DateEnd,string PurChase_InOut)
        {
            string sql = "select Purchase_Id,Purchase_curom,Purchase_date,Purchase_State,SumMoney,p.Supplier_Id,p.WareHose_Id,p.Employee_Id,Supplier_Name,WareHose_Name,Employee_Name from Purchase p join Supplier s on p.Supplier_Id=s.Supplier_Id join WareHose w on p.WareHose_Id=w.WareHose_Id join Employee e on p.Employee_Id=e.Employee_Id WHERE Purchase_date BETWEEN CONVERT(datetime,'" + DateStart + "') AND CONVERT(datetime,'" + DateEnd + "') and PurChase_InOut='" + PurChase_InOut + "'";
            List<Purchase> list = new List<Purchase>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Purchase p = new Purchase();
                p.Purchase_Id = Convert.ToInt32(item["Purchase_Id"]);
                p.Purchase_curom = item["Purchase_curom"].ToString();
                p.Purchase_date = Convert.ToDateTime(item["Purchase_date"]);
                p.Purchase_State = Convert.ToInt32(item["Purchase_State"]);
                p.SumMoney = Convert.ToInt32(item["SumMoney"]);
                p.Supplier_Id = Convert.ToInt32(item["Supplier_Id"]);
                p.WareHose_Id = Convert.ToInt32(item["WareHose_Id"]);
                p.Employee_Id = Convert.ToInt32(item["Employee_Id"]);
                p.Supplier_Name = item["Supplier_Name"].ToString();
                p.WareHose_Name = item["WareHose_Name"].ToString();
                p.Employee_Name = item["Employee_Name"].ToString();

                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 总金额
        /// </summary>
        /// <param name="Year"></param>
        /// <param name="MonthStart"></param>
        /// <param name="MonthEnd"></param>
        /// <param name="PurChase_InOut"></param>
        /// <returns></returns>
        public int SumMoney(string DateStart, string DateEnd, string PurChase_InOut)
        {
            string sql = "SELECT SUM(SumMoney) FROM Purchase WHERE Purchase_date BETWEEN CONVERT(datetime,'"+DateStart+"') AND CONVERT(datetime,'"+DateEnd+"') and PurChase_InOut='"+PurChase_InOut+"'";


            if (h.ExecScalar(sql)==System.DBNull.Value)
            {
                return 0;
            }
            return Convert.ToInt32( h.ExecScalar(sql));
        }
    }
}
